﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using Word = Microsoft.Office.Interop.Word;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace ConcordWinManager
{
    public class Template
    {
        public Template()
        {
            this.rezident = new Rezident();   
        }

        public int id_template;

        public int id_rezident;

        public Rezident rezident;

        public string Name;

        public byte[] Body;

        public string Note;

        public string crc32;

        public string Filename;

        public string DocType;

        public string SourceFilePath;

        public void File2SqlBlob(string SourceFilePath, int id)
        {
            try
            {
                SqlConnection cn = new SqlConnection(DB.sc_str.ToString());

                string query = string.Format("UPDATE dbo.Template SET Body={0}, crc32='{1}' WHERE id_template='{2}'", 
                    "@Body",
                    this.crc32,
                    id);

                SqlCommand cmd = new SqlCommand(query, cn);

                FileStream fs = new FileStream(SourceFilePath, FileMode.Open, FileAccess.Read);

                Byte[] b = new Byte[fs.Length];
                fs.Read(b, 0, b.Length);
                fs.Close();

                SqlParameter P = new SqlParameter("@Body", SqlDbType.VarBinary, b.Length,
                    ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, b);

                cmd.Parameters.Add(P);
                cn.Open();
                if (cmd.ExecuteNonQuery() == 1)
                {
                    //MessageBox.Show("Your images stored successfully");
                }
                cn.Close();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }

        public void SqlBlob2File(string DestFilePath, int id)
        {
            try
            {
                int PictureCol = 0; // the column # of the BLOB field
                SqlConnection cn = new SqlConnection(DB.sc_str.ToString());
                SqlCommand cmd = new SqlCommand(string.Format("SELECT Body FROM dbo.Template WHERE id_template='{0}'", id), cn);
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                dr.Read();
                Byte[] b = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
                dr.GetBytes(PictureCol, 0, b, 0, b.Length);
                dr.Close();
                cn.Close();
                System.IO.FileStream fs =
                 new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);

                fs.Write(b, 0, b.Length);
                fs.Close();
                //MessageBox.Show("Image written to file successfully");
            }
            catch (Exception ex)
            {
                 System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }
        
        public void Insert()
        {
            try
            {
                SqlConnection cn = new SqlConnection(DB.sc_str.ToString());

                string query = string.Format("INSERT INTO dbo.Template (Name_template, Filename, Note, crc32, DocType) " +
                    " Values ('{0}','{1}','{2}','{3}','{4}')",
                    this.Name,
                    this.Filename,
                    this.Note,
                    this.crc32,
                    this.DocType
                    );

                DB.execute_sql(query);

                this.id_template = DB.max_num("dbo.Template", "id_template");

                File2SqlBlob(this.SourceFilePath, this.id_template);
            }
            catch (SqlException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }

        public void SelectByType(string type, ref DataSet ds)
        {
            string query = string.Format("SELECT * FROM dbo.View_Template_rus WHERE DocType = '{0}'", type);

            DB.query_table( query, "dbo.View_Template_rus", ref ds);
        }


        /// <summary>
        /// Запрос квитанции формы ПД-4
        /// </summary>
        /// <param name="type"></param>
        /// <param name="ds"></param>
        public void SelectPD4(string type, ref DataSet ds)
        {
            string query = string.Format("SELECT id_template, id_rezident, Name_template, Filename, Note, crc32, DocType FROM dbo.Template WHERE DocType = '{0}'", type);

            DB.query_table(query, "dbo.Template", ref ds);

            if (int.TryParse(ds.Tables["dbo.Template"].Rows[0]["id_template"].ToString(), out this.id_template))
            {
                ;
            }

            this.Name = ds.Tables["dbo.Template"].Rows[0]["Name_template"].ToString();
            this.Filename = ds.Tables["dbo.Template"].Rows[0]["Filename"].ToString();
            this.DocType = ds.Tables["dbo.Template"].Rows[0]["DocType"].ToString();
            this.Note = ds.Tables["dbo.Template"].Rows[0]["Note"].ToString();
            this.crc32 = ds.Tables["dbo.Template"].Rows[0]["crc32"].ToString();

            if (Filename.Length > 0)
            {
                string pathDir = Path.GetTempPath() + @"ConcordWinManager\templates\" + this.id_template; //заменить на НАСТРОЙКУ из Settings.cs
                string pathFile = pathDir + @"\" + this.Filename + ".gz";

                if (!Directory.Exists(pathDir))//Проверяем, есть ли такая папка
                {
                    Directory.CreateDirectory(pathDir);
                }

                if (!File.Exists(pathFile)) //Проверяем, есть ли такой файл
                {
                    SqlBlob2File(pathFile, this.id_template); //Получаем файл из БД                        
                }
                else
                {
                    CRC32Helper crc32 = new CRC32Helper();
                    String hash = String.Empty;

                    using (FileStream fs = File.Open(pathFile, FileMode.Open))
                    {
                        foreach (byte b in crc32.ComputeHash(fs))
                        {
                            hash += b.ToString("x2").ToLower();
                        }
                    }

                    if (this.crc32 != hash)
                    {
                        SqlBlob2File(pathFile, this.id_template);
                    }
                }

                this.SourceFilePath = pathFile;
            }
        }

        public void SelectById(int id)
        {
            DataSet ds = new DataSet();

            string query = string.Format("SELECT id_template, id_rezident, Name_template, Filename, Note, crc32, DocType FROM dbo.Template WHERE id_template = '{0}'", id);

            DB.query_table(query, "dbo.Template", ref ds);

            if (int.TryParse(ds.Tables["dbo.Template"].Rows[0]["id_template"].ToString(), out this.id_template))
            {
                ;
            }

            this.Name = ds.Tables["dbo.Template"].Rows[0]["Name_template"].ToString();
            this.Filename = ds.Tables["dbo.Template"].Rows[0]["Filename"].ToString();
            this.DocType = ds.Tables["dbo.Template"].Rows[0]["DocType"].ToString();
            this.Note = ds.Tables["dbo.Template"].Rows[0]["Note"].ToString();
            this.crc32 = ds.Tables["dbo.Template"].Rows[0]["crc32"].ToString();

            if (Filename.Length > 0)
            {
                string pathDir = Path.GetTempPath() + @"ConcordWinManager\templates\" + this.id_template; //заменить на НАСТРОЙКУ из Settings.cs
                string pathFile = pathDir + @"\" + this.Filename + ".gz";

                if (!Directory.Exists(pathDir))//Проверяем, есть ли такая папка
                {
                    Directory.CreateDirectory(pathDir);
                }

                if (!File.Exists(pathFile)) //Проверяем, есть ли такой файл
                {
                    SqlBlob2File(pathFile, id); //Получаем файл из БД                        
                }
                else
                {
                    CRC32Helper crc32 = new CRC32Helper();
                    String hash = String.Empty;

                    using (FileStream fs = File.Open(pathFile, FileMode.Open))
                    {
                        foreach (byte b in crc32.ComputeHash(fs))
                        {
                            hash += b.ToString("x2").ToLower();
                        }
                    }

                    if (this.crc32 != hash)
                    {
                        SqlBlob2File(pathFile, id);
                    }
                }

                this.SourceFilePath = pathFile;
            }
        }

        public void SelectAll(ref DataSet ds)
        {
            ;
        }

        public void Update()
        {
            string query = string.Format("UPDATE dbo.Template SET Name_template='{0}', " +
                " Filename='{1}', Note='{2}', DocType='{3}' WHERE (id_template= '{4}')",
                this.Name,
                this.Filename,
                this.Note,
                this.DocType,
                this.id_template);

            DataSet ds = new DataSet();

            DB.query_table(query, "dbo.Rezident", ref ds);

            File2SqlBlob(this.SourceFilePath, this.id_template);
        }

        public void DeleteById()
        {
            ;
        }

        /// <summary>
        /// Генерация документа путем открытия шаблона и замены ключевых слов
        /// </summary>
        /// <param name="liKeyWordsFrom">Ключевые слова для замены</param>
        /// <param name="liKeyWordsTo">На что заменять</param>
        /// <param name="wrd"></param>
        /// <param name="suffix">Добавочный суффикс для названия файла</param>
        public void GenerateDocument(Rassrochka rassrochka, 
            List<string> liKeyWordsFrom, 
            List<string> liKeyWordsTo, 
            ref Microsoft.Office.Interop.Word.Application wrd, 
            string suffix)
        {
            FileInfo fi = new FileInfo(this.SourceFilePath);
            GZipHelper.Decompress(fi);

            Random rnd=new Random();
            FileInfo fi_source = new FileInfo(Path.Combine(Path.Combine(Path.GetTempPath(), @"ConcordWinManager\templates\" + this.id_template.ToString()), this.Filename));
            FileInfo fi_destination = new FileInfo(Path.GetTempPath() + @"ConcordWinManager\templates\tmp\" + fi_source.Name + "[" + rnd.Next(1000).ToString() + "]" + "--" + suffix + fi_source.Extension);
            fi_source.CopyTo(fi_destination.FullName, true);

            object template = "";

            //object template = string.Format(@"{0}\{1}\{2}",
            //    Path.GetTempPath(),
            //    this.id_template,
            //    this.Filename);

            object newtemplate = false;
            object documenttype = Word.WdNewDocumentType.wdNewBlankDocument;
            object visible = true;

            object filePath = fi_destination.FullName;

            object missing = Type.Missing;

            //wrd.Documents.Add(ref template, ref newtemplate, ref documenttype, ref visible);

            wrd.Documents.Open(ref filePath,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref missing,
                ref visible,
                ref missing,
                ref missing,
                ref missing,
                ref missing);



            object findText;
            object replaceText;
            object replaceTrue = true;

            for (int i = 0; i < liKeyWordsFrom.Count; )
            {
                findText = liKeyWordsFrom[i];
                replaceText = liKeyWordsTo[i];

                wrd.Selection.Find.Wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue;

                wrd.Selection.Find.Execute(ref findText, ref replaceTrue, ref replaceTrue, ref missing,
                    ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref replaceTrue, ref replaceTrue, ref missing, ref missing, ref missing, ref missing);


                object wdBeh=Word.WdDefaultTableBehavior.wdWord9TableBehavior;
                object wdAutoFit=Word.WdAutoFitBehavior.wdAutoFitContent;
                object wdChar=Word.WdUnits.wdCharacter;
                object wdLine=Word.WdUnits.wdLine;
                object NumColumns=2;
                object Separator=Word.WdTableFieldSeparator.wdSeparateByTabs;               

                if (wrd.Selection.Find.Found)
                {
                    wrd.Selection.Text = liKeyWordsTo[i];

                    if (liKeyWordsFrom[i] == "[таблица_рассрочки]")
                    {
                        string[] s = liKeyWordsTo[i].Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
                        object NumRows = s.Length;

                        wrd.Selection.ConvertToTable(ref Separator, ref NumRows, ref  NumColumns, ref missing, ref  missing, ref  missing, ref missing,
                            ref missing, ref  missing, ref  missing, ref  missing, ref missing, ref  missing, ref  wdAutoFit, ref  wdAutoFit, ref  wdBeh);

                    }
                }
                else
                {
                    i++;
                }
            }

            wrd.Visible = true;

            object noprompt = true;
            object originalformat = Word.WdOriginalFormat.wdWordDocument;

            wrd.Documents.Save(ref noprompt, ref originalformat);
        }

    }
}
